package xupt.se.ttms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import xupt.se.ttms.idao.iStudioDAO;
import xupt.se.ttms.model.Studio;
import xupt.se.util.MyDBUtil;



public class StudioDAO implements iStudioDAO {
	
	private final String insertSql="insert into T_Studio(name,rowCounts,colCount,seatnumber,introduction)values(?,?,?,?,?)";
	private final String deleteSql="delete from T_Studio where id=?";	
	private final String updateSql="update T_Studio set name=?,rowCounts=?,colCount=?,seatnumber=?,introduction=? where id=?";
	private final String selectSqlInt="select * from T_Studio where id=?";
	private final String selectSqlStr="select * from T_Studio where name=?";
	private final String selectSqlAll="select * from T_Studio ";
	private final String selectSmall="select * from T_Studio where seatnumber<?";
	private final String selectBig="select * from T_Studio where seatnumber>=?";
	public boolean execInsert(Studio studio) throws Exception{
		
		String sql=insertSql;
		Connection conn=null;
		boolean flag=false;
		PreparedStatement ps=null;
		MyDBUtil db = new MyDBUtil();
		conn=db.openConnection();
		try {
			if(null==conn)
				throw new Exception("Database not connected!");
			
			ps=conn.prepareStatement(sql);

			ps.setString(1,studio.getName());
			ps.setInt(2,studio.getRowCount());
			ps.setInt(3,studio.getColCount());
			ps.setInt(4,studio.getSeatnumber());
			ps.setString(5,studio.getIntroduction());

			flag=ps.execute();
			/*
			Statement stmt = conn.createStatement();
			flag = stmt.execute(sql);*/			
			ps.close();	
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	public boolean execUpdate(Studio studio) throws Exception{
		String sql=updateSql;
		Connection conn=null;
		boolean flag=false;
		PreparedStatement ps=null;
		MyDBUtil db = new MyDBUtil();
		conn=db.openConnection();
		try {
			if(null==conn)
				throw new Exception("Database not connected!");
			
			ps=conn.prepareStatement(sql);
	
			ps.setString(1,studio.getName());
			ps.setInt(2,studio.getRowCount());
			ps.setInt(3,studio.getColCount());
			ps.setInt(4, studio.getSeatnumber());
			ps.setString(5,studio.getIntroduction());
			ps.setInt(6, studio.getID());

			flag=ps.execute();
			/*
			Statement stmt = conn.createStatement();
			flag = stmt.execute(sql);*/			
			ps.close();	
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * 查询，传一个类型String,防sql注入攻击
	 * @param sql		sql语句
	 * @param str		where条件
	 * @return
	 * @throws Exception
	 */
	public ResultSet execQuery(String str) throws Exception {
		String sql=selectSqlStr;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execQuery(sql, str);
		return rstSet;
	}
	/**
	 * 查询，传一个类型int,防sql注入攻击
	 * @param sql		sql语句
	 * @param i			where条件
	 * @return
	 * @throws Exception
	 */
	public ResultSet execQuery(int i) throws Exception {
		String sql=selectSqlInt;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execQuery(sql, i);
		return rstSet;
	}	
	/**
	 * 删除 数据，传一个类型int，防sql注入攻击
	 * @param sql
	 * @param i
	 * @return
	 * @throws Exception
	 */
	public boolean execDelete(int i) throws Exception{
		String sql=deleteSql;
		boolean flag = false;
		MyDBUtil myDBUtil=new MyDBUtil();
		flag=myDBUtil.execDelete(sql, i);
		return flag;
	}
	@Override
	public ResultSet exexQueryAll() throws Exception {
		String sql=selectSqlAll;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execAllQuery(sql);
		return rstSet;
	}
	@Override
	public List<Studio> HandleStudioResultSet(ResultSet rs) throws SQLException {
		 //结果，在第一个查询结果有个rs标记，通过rs.next()遍历获取全部
		Studio studio=null;
		List<Studio>studios=new ArrayList<>(); 
		 //获取结果的数据
        ResultSetMetaData rsmd = rs.getMetaData();
        //获取该条结果有多少个列数据
        int columnCount = rsmd.getColumnCount();
       
    	System.out.println("获取演出厅数据中");
    	 //通过rs.next()遍历获取全部结果
        while (rs.next()) {
        
        	studio=new Studio();
       	 //输出每条结果的全部数据
           for (int i = 0; i < columnCount; i++) {

        	
        	   studio.setID(rs.getInt("id"));
        	   studio.setName(rs.getString("name"));
        	   studio.setRowCount(rs.getInt("rowCounts"));
        	   studio.setColCount(rs.getInt("colCount"));
        	   studio.setSeatnumber(rs.getInt("seatnumber"));
        	   studio.setIntroduction(rs.getString("introduction"));
        	   
        	 
               System.out.print(rs.getString(i+1) + "\t");

           }
           studios.add(studio);
           System.out.println();		                    
        }
        
       return studios;
	}
	@Override
	public ResultSet execQuerySmall(int rowCounts) throws Exception {
		String sql=selectSmall;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execQuery(sql,rowCounts);
		return rstSet;
	}
	@Override
	public ResultSet execQueryBig(int rowCounts) throws Exception {
		String sql=selectBig;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execQuery(sql,rowCounts);
		return rstSet;
	}

	/*
	@Override	
	public int insert(Studio stu) {
		try {
			String sql = "insert into studio(studio_name, studio_row_count, studio_col_count, studio_introduction )"
					+ " values('"
					+ stu.getName()
					+ "', "
					+ stu.getRowCount()
					+ ", " + stu.getColCount() 
					+ ", '" + stu.getIntroduction()
					+ "' )";
			DBUtil db = new DBUtil();
			db.openConnection();
			ResultSet rst = db.getInsertObjectIDs(sql);
			if (rst!=null && rst.first()) {
				stu.setID(rst.getInt(1));
			}
			db.close(rst);
			db.close();
			return 1;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return 0;
	}

	@Override
	public int update(Studio stu) {
		int rtn=0;
		try {
			String sql = "update studio set " + " studio_name ='"
					+ stu.getName() + "', " + " studio_row_count = "
					+ stu.getRowCount() + ", " + " studio_col_count = "
					+ stu.getColCount() + ", " + " studio_introduction = '"
					+ stu.getIntroduction() + "' ";

			sql += " where studio_id = " + stu.getID();
			DBUtil db = new DBUtil();
			db.openConnection();
			rtn =db.execCommand(sql);
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtn;
	}

	@Override
	public int delete(int ID) {
		int rtn=0;		
		try{
			String sql = "delete from  studio ";
			sql += " where studio_id = " + ID;
			DBUtil db = new DBUtil();
			db.openConnection();
			rtn=db.execCommand(sql);
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtn;		
	}

	@Override
	public List<Studio> select(String condt) {
		List<Studio> stuList = null;
		stuList=new LinkedList<Studio>();
		try {
			String sql = "select studio_id, studio_name, studio_row_count, studio_col_count, studio_introduction from studio ";
			condt.trim();
			if(!condt.isEmpty())
				sql+= " where " + condt;
			DBUtil db = new DBUtil();
			if(!db.openConnection()){
				System.out.print("fail to connect database");
				return null;
			}
			ResultSet rst = db.execQuery(sql);
			if (rst!=null) {
				while(rst.next()){
					Studio stu=new Studio();
					stu.setID(rst.getInt("studio_id"));
					stu.setName(rst.getString("studio_name"));
					stu.setRowCount(rst.getInt("studio_row_count"));
					stu.setColCount(rst.getInt("studio_col_count"));
					stu.setIntroduction(rst.getString("studio_introduction"));
					stuList.add(stu);
				}
			}
			db.close(rst);
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			
		}
		
		return stuList;
	}
	*/
	
}
